package com.zx.oa.dao.student;

import com.zx.oa.dao.BaseDao;
import com.zx.oa.entity.ShowStudent;
import com.zx.oa.entity.Student;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDaoImpl extends BaseDao implements StudentDao{

    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    @Override
    public List<ShowStudent> getStudentList(String name, String className, String major, int currentPageNo, int pageSize) {
        List<ShowStudent> showStudentList=new ArrayList<>();
        //可变长度字符串
        StringBuffer sb=new StringBuffer("SELECT stu.`studentId`,stu.`studentName`,maj.`major`,cla.`className`,stu.`fromSchool`,stu.`education` \n" +
                "FROM ztestudent stu,zteclassinfo cla,ztemajor maj\n" +
                "WHERE stu.`classId`=cla.`classId`\n" +
                "AND cla.`studyType`=maj.`id`");
        conn=this.getConnection();
        //按照顺序给？拼接参数的值
        List<Object> params=new ArrayList<>();
        if(null!=name&&!"".equals(name)){
            sb.append(" AND  stu.`studentName` LIKE ?");
            params.add("%"+name+"%");
        }
        if(null!=className&&!"".equals(className)){
            sb.append(" AND  cla.`className` LIKE ?");
            params.add("%"+className+"%");
        }
        if(null!=major&&!"".equals(major)){
            sb.append(" AND  maj.`major` LIKE ?");
            params.add("%"+major+"%");
        }
        sb.append(" LIMIT ?,?");
        params.add((currentPageNo-1)*pageSize);
        params.add(pageSize);
        try {
            pstmt = conn.prepareStatement(sb.toString());
            //依次给?参数赋值
            for(int i=0;i<params.size();i++){
                pstmt.setObject((i+1),params.get(i));
            }
            rs = pstmt.executeQuery();
            while(rs.next()){
                Integer StuId=rs.getInt(1);
                String stuName=rs.getString(2);
                String stuMajor=rs.getString(3);
                String stuClass=rs.getString(4);
                String stuSchool=rs.getString(5);
                String stuEducation=rs.getString(6);
                ShowStudent showStudent=new ShowStudent(StuId,stuName,stuMajor,stuClass,stuSchool,stuEducation);
                showStudentList.add(showStudent);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            closeAll(conn,pstmt,rs);
        }
        return showStudentList;
    }

    @Override
    public int getStudentTotalCount(String name, String className, String major) {
        int count=-1;//总记录数
        //可变长度字符串
        StringBuffer sb = new StringBuffer("SELECT COUNT(*)\n" +
                "FROM ztestudent stu,zteclassinfo cla,ztemajor maj\n" +
                "WHERE stu.`classId`=cla.`classId`\n" +
                "AND cla.`studyType`=maj.`id`");
        conn = this.getConnection();
        //按照顺序给？拼接参数的值
        List<Object>params=new ArrayList<>();
        if(null!=name&&!"".equals(name)){
            sb.append(" AND  stu.`studentName` LIKE ?");
            params.add("%"+name+"%");
        }
        if(null!=className&&!"".equals(className)){
            sb.append(" AND  cla.`className` LIKE ?");
            params.add("%"+className+"%");
        }
        if(null!=major&&!"".equals(major)){
            sb.append(" AND  maj.`major` LIKE ?");
            params.add("%"+major+"%");
        }
        try {
            pstmt = conn.prepareStatement(sb.toString());
            //依次给?参数赋值
            for(int i=0;i<params.size();i++){
                pstmt.setObject((i+1),params.get(i));
            }
            rs = pstmt.executeQuery();
            while (rs.next()) {
                count=rs.getInt(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            closeAll(conn,pstmt,rs);
        }
        return count;
    }

    public boolean addStudent(Student stu) {

        String sql = "INSERT INTO ztestudent (studentName,classId,fromSchool,education,loginCode,PASSWORD) VALUES (?,?,?,?,?,?)";
        try {
            conn = this.getConnection();
            pstmt  = conn.prepareStatement(sql);

            pstmt.setString(1,stu.getStudentName());
            pstmt.setInt(2,stu.getClassId());
            pstmt.setString(3,stu.getFromSchool());
            pstmt.setString(4,stu.getEducation());
            pstmt.setString(5,stu.getLoginCode());
            pstmt.setString(6,stu.getPassword());

            int count = pstmt.executeUpdate();
            if (count == 1){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            this.closeAll(conn,pstmt,rs);
        }

        return false;
    }

    @Override
    public List<ShowStudent> getStudentListAll(String name, String className, String major) {
        List<ShowStudent> showStudentList=new ArrayList<>();
        //可变长度字符串
        StringBuffer sb=new StringBuffer("SELECT stu.`studentId`,stu.`studentName`,maj.`major`,cla.`className`,stu.`fromSchool`,stu.`education` \n" +
                "FROM ztestudent stu,zteclassinfo cla,ztemajor maj\n" +
                "WHERE stu.`classId`=cla.`classId`\n" +
                "AND cla.`studyType`=maj.`id`");
        conn=this.getConnection();
        //按照顺序给？拼接参数的值
        List<Object> params=new ArrayList<>();
        if(null!=name&&!"".equals(name)){
            sb.append(" AND  stu.`studentName` LIKE ?");
            params.add("%"+name+"%");
        }
        if(null!=className&&!"".equals(className)){
            sb.append(" AND  cla.`className` LIKE ?");
            params.add("%"+className+"%");
        }
        if(null!=major&&!"".equals(major)){
            sb.append(" AND  maj.`major` LIKE ?");
            params.add("%"+major+"%");
        }
        try {
            pstmt = conn.prepareStatement(sb.toString());
            //依次给?参数赋值
            for(int i=0;i<params.size();i++){
                pstmt.setObject((i+1),params.get(i));
            }
            rs = pstmt.executeQuery();
            while(rs.next()){
                Integer StuId=rs.getInt(1);
                String stuName=rs.getString(2);
                String stuMajor=rs.getString(3);
                String stuClass=rs.getString(4);
                String stuSchool=rs.getString(5);
                String stuEducation=rs.getString(6);
                ShowStudent showStudent=new ShowStudent(StuId,stuName,stuMajor,stuClass,stuSchool,stuEducation);
                showStudentList.add(showStudent);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            closeAll(conn,pstmt,rs);
        }
        return showStudentList;
    }

    @Override
    public boolean delStudent(Integer id) {
        String sql="DELETE FROM ztestudent WHERE studentId=?";

        Object [] params={id};

        if(this.executeUpdateInfo(sql,params)>0){
            return  true;
        }

        return false;
    }

    @Override
    public Student getStudentById(Integer id) {
        Student stu=null;
        conn=this.getConnection();
        String sql="SELECT * FROM ztestudent WHERE studentId=?";
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,id);
            rs=pstmt.executeQuery();
            while (rs.next()){
                Integer stuId=rs.getInt(1);
                String stuName=rs.getString(2);
                Integer classId=rs.getInt(3);
                String fromSchool=rs.getString(4);
                String education=rs.getString(5);
                String loginCode=rs.getString(6);
                String password=rs.getString(7);
                stu=new Student(stuId,stuName,classId,fromSchool,education,loginCode,password);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            closeAll(conn,pstmt,rs);
        }
        return stu;
    }

    @Override
    public boolean modifyStudent(Student student) {
        String sql="UPDATE ztestudent stu SET stu.`studentName`=?,stu.`classId`=?,stu.`fromSchool`=?,stu.`education`=?,stu.`loginCode`=?,stu.`password`=? WHERE stu.`studentId`=? ";

        Object [] params={
                student.getStudentName(),
                student.getClassId(),
                student.getFromSchool(),
                student.getEducation(),
                student.getLoginCode(),
                student.getPassword(),
                student.getStudentId()
        };

        if(this.executeUpdateInfo(sql,params)>0){
            return  true;
        }

        return false;
    }

    @Override
    public boolean getClassInfoCount(int classId) {
        String sql="SELECT COUNT(*) FROM ztestudent WHERE classId=?";
       int count=0;
        try {
            conn=this.getConnection();
            pstmt=conn.prepareStatement(sql);
            pstmt.setObject(1,classId);

            rs=pstmt.executeQuery();
            while (rs.next()){
                count=rs.getInt(1);
            }
            if (count<=0){
                return true;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            this.closeAll(conn,pstmt,rs);
        }
        return false;
    }

}
